package com.wuseguang.springcloud.service.impl;


import com.wuseguang.springcloud.Classcardservice.ClassCardFieldService;
import com.wuseguang.springcloud.Filter.RequestParameterWrapper;
import com.wuseguang.springcloud.dao.jdbc.ClassDutyJdbc;
import com.wuseguang.springcloud.entity.ClassDutyList;
import com.wuseguang.springcloud.service.ClassDutyService;
import com.wuseguang.springcloud.util.CodeUtil;
import com.wuseguang.springcloud.util.DateUtil;
import com.wuseguang.springcloud.util.ExcelDownloadUtils;
import com.wuseguang.springcloud.util.Result;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;


import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;

@Service
public class ClassDutyImpl implements ClassDutyService {

    @Autowired
    private ClassCardFieldService classCardFieldService;

    private Logger logger = LoggerFactory.getLogger(this.getClass());


    @Autowired
    private ClassDutyJdbc classDutyJdbc;

    private String model = "dutyflag";

    private String flag = "1";

    /**
     * @描述 查询所有值日信息
     * @创建人 zm
     * @创建时间 2020/1/22
     */
    @Override
    public Map<String, Object> queryClassDutyAll(int school_id, int classid) {
        String sql = "SELECT id,projects,groups,school_id,class_id,DATE(starttime) starttime,type,`index`  FROM class_duty_time WHERE school_id=? AND class_id=? LIMIT 1";
        Map<String, Object> maps = null;
        try {
            maps = classDutyJdbc.queryMap(sql, school_id, classid);
        } catch (Exception e) {
            logger.info("queryClassDutyAll==>maps,error={}", e);
        }
        if (maps == null) {
            return maps;
        }
        List<Map<String, Object>> maps1 = null;
        String sql1 = "SELECT d.id,d.student_id tid,t.student_name name,d.x,d.y FROM class_duty d LEFT JOIN student t ON d.student_id=t.id WHERE d.school_id=? AND d.class_id=? ORDER BY Y,X";
        try {
            maps1 = classDutyJdbc.queryListMap(sql1, school_id, classid);
        } catch (Exception e) {
            logger.info("queryClassDutyAll==>maps1,error={}", e);
        }
        maps.put("list", maps1);
        return maps;
    }


    /**
     * @描述 批量添加值日信息
     * @创建人 zm
     * @创建时间 2020/1/22
     */
    @Override
    public Result addClassDuty(int school_id, ClassDutyList classDutyList) {
        try {
            Integer classid = classDutyList.getClassid();
            String sql = "SELECT  id,student_name name FROM student WHERE school_id=? AND  class_id=?";
            List<Map<String, Object>> list = classDutyJdbc.queryListMap(sql, school_id, classid);
            List<Map<String, Object>> mapList = classDutyJdbc.batchaddClassDuty(school_id, classDutyList, list);
            if (mapList == null || mapList.size() <= 0) {
                classCardFieldService.classesAndCards(classDutyList.getClassid(), model, flag);
                return new Result();
            }
            return new Result(CodeUtil.RETRIEVE_AND_REPORT_ERRORS, classDutyList.getList());
        } catch (Exception e) {
            logger.info("addClassDuty==>error={}", e);
        }
        return new Result(CodeUtil.RETRIEVE_AND_REPORT_ERRORS, classDutyList.getList());
    }

    /**
     * @描述 根据班级查学生
     * @创建人 zm
     * @创建时间 2020/3/15
     */
    @Override
    public List<Map<String, Object>> findStudentByClassId(Integer classid) {
        String sql = "SELECT id,student_name name FROM student WHERE class_id=? and state=0";
        logger.info("根据班级，查询学生信息 sql = {}", sql);
        return classDutyJdbc.queryListMap(sql, classid);
    }

    /**
     * @描述 自动导入
     * @创建人 zm
     * @创建时间 2020/3/15
     */
    @Override
    public Result autoImportDuty(Integer classid, Integer x, Integer y) {
        logger.info("x={},y={}", x, y);
        String sql = "SELECT student_name name FROM student WHERE class_id=? and state=0";
        logger.info("autoImportDuty,sql = {}", sql);
        List<String> list = classDutyJdbc.queryListString(sql, new Object[]{classid});
        Collections.shuffle(list);
        List<Map> maps = new ArrayList<>();
        int a = 0;
        for (int i = 0; i <= y; i++) {//坐标算法
            for (int j = 0; j <= x; j++) {
                Map<String, Object> map = new HashMap<>();
                if (a < list.size()) {
                    map.put("x", j);
                    map.put("y", i);
                    map.put("name", list.get(a));
                } else {//没有时处理
                    map.put("x", j);
                    map.put("y", i);
                    map.put("name", "");
                }
                maps.add(map);
                a++;
            }
        }
        return new Result(maps);
    }

    /**
     * @描述 导出值日信息
     * @创建人 zm
     * @创建时间 2020/3/15
     */
    @Override
    public void exportDuty(RequestParameterWrapper request, HttpServletResponse response, Integer classId) {

        //学校id
        String schoolId = request.getParam("schoolId");

        Map<String, Object> map = queryClassDutyAll(Integer.parseInt(schoolId), classId);
        List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("list");
        String projects = (String) map.get("projects");
        String groups = (String) map.get("groups");
        try {
            String[] split = projects.split(",");
            String[] split1 = groups.split(",");
            downloadExcelMapTemplate(response, list, split, split1, "班级值日");
        } catch (IOException e) {
            logger.info("导出学生值日错误,error={}", e);
        }

    }


    public void downloadExcelMapTemplate(HttpServletResponse response, List<Map<String, Object>> list, String[] key, String[] value, String fileName) throws IOException {


        //创建ByteArrayOutputStream
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        //创建工作簿
        createWorkBookTimetable(list, key, value).write(os);
        //下载execl
        ExcelDownloadUtils.downloadExcel(response, os, fileName);
    }

    public Workbook createWorkBookTimetable(List<Map<String, Object>> list, String[] key, String value[]) {

        // 创建excel工作簿
        Workbook worb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = worb.createSheet("sheet1");
        // 手设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度。
        for (int i = 0; i < key.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35 * 150));
        }

        // 创建单元格格式
        CellStyle cs = worb.createCellStyle();
        // 创建字体
        Font f = worb.createFont();

        // 创建字体样式
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setFontName("宋体");

        // 设置单元格的样式
        cs.setFont(f);


        // 创建第一行
        Row row = sheet.createRow((short) 0);
        row.setHeightInPoints(20);

        Cell cell = null;


        // 创建单元格格式
        CellStyle cs2 = worb.createCellStyle();
        // 创建字体
        Font f2 = worb.createFont();

        // 创建字体样式
        f2.setFontHeightInPoints((short) 12);
        f2.setColor(IndexedColors.RED.getIndex());
        f2.setFontName("宋体");

        // 设置单元格的样式
        cs2.setFont(f2);


        // 创建第一行
        Row row2 = sheet.createRow((short) 1);
        row2.setHeightInPoints(20);
        cell = row.createCell(0);
        cell.setCellValue("分组\\项目");
        cell.setCellStyle(cs2);
        // 设置列名
        for (int i = 0; i < key.length; i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue(key[i]);
            cell.setCellStyle(cs2);
        }
        // 设置每行每列的值
        if (!list.isEmpty()) {
            for (short i = 0; i < value.length; i++) {
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow((short) (i + 1));
                row1.setHeightInPoints(20);
                cell = row1.createCell(0);
                //把值放进去
                cell.setCellValue(value[i]);
                //设置样式
                cell.setCellStyle(cs2);
                // 在row行上创建一个方格
                int a = 0;
                for (short j = 0; j < list.size(); j++) {
                    int y = (int) list.get(j).get("y");
                    if (i == y) {
                        a++;
                        cell = row1.createCell(a);
                        // 判断是否为时间格式，如果为时间格式，要进行格式转换
                        String valueS = "";
                        Object bufferValue = list.get(j).get("name");
                        if (bufferValue instanceof Date) {
                            //获取value
                            valueS = DateUtil.parseData((Date) bufferValue, "yyyy-MM-dd HH:mm:ss");
                        } else {
                            //获取value
                            valueS = StringUtils.isEmpty(bufferValue) ? "" : bufferValue.toString();
                        }
                        //把值放进去
                        cell.setCellValue(valueS);
                        //设置样式
                        cell.setCellStyle(cs);
                    }
                }
            }
        }
        return worb;
    }
}
